Zdroje - Výuka
--------------------
C# Tutorial - How to Connect and Use Local Database in Visual Studio 2019 | FoxLearn
https://www.youtube.com/watch?v=mgtfxtjKoaA

Connection String to Connect to .MDF
https://stackoverflow.com/questions/928813/connection-string-to-connect-to-mdf
For a local DB and no SQLEXPRESS server:
"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=""|DataDirectory|\MyDB.mdf"";Integrated Security=True;Connect Timeout=30"

MS-SQL databáze krok za krokem - Online kurz
Zdroj: https://www.itnetwork.cz/ms-sql
https://www.itnetwork.cz/ms-sql

Databáze v C# - ADO.NET	 - Online kurz
Zdroj: https://www.itnetwork.cz/csharp/databaze
https://www.itnetwork.cz/csharp/databaze

OOP
C# tutorial for beginners
https://www.youtube.com/watch?v=r3CExhZgZV8&list=PLZPZq0r_RZOPNy28FDBys3GVP2LiaIyP_

chatgpt
https://chatgpt.com/
Dotazy SQL, datagridview



Instalace, zprovoznění a tvorba databáze
---------------------------------------------------
0/Instalace a zprovoznění i bez Visual Studia
install and start error solution
0/1.install
net framework 4.7.2,  net framework 4.8 
MS SQL SERVER 2022 Express -> zvolit localdb
visual c++ Redistributable Packages 2008,2012,2013,2015,2015-2022

0/2.Řešení možných problému 
sqllocaldb delete MSSQLLocalDB
sqllocaldb create MSSQLLocalDB
sqllocaldb start MSSQLLocalDB


1/vytvořit winforms net  - 02-winform-dat
2/project - přidat novou položku -> database založená na službách
3/průzkumnik serveru - získat propojovací řetěřec(soubor.mdf pak kliknout na properties a zkopirovat propojovací řetěřec a upravit na DataDirectory (attachdbFilename) 
4/průzkumnik server soubor.mdf vytvořit tabulku  v tabulky a nastavit v design id v index  properties klikem do indexu Identity specifikation na true a increment a seed na 1
5/přidat polozky v design tabulce graficky nebo pomoci t-sql pak ctrl-s pro ulozeni tabulky a upload
5/přidani dat do tabulky guio nebo t-sql, uložení a upload tabulky
6/průzkumnik serveru aktualizovat tabulky a dat zobrazit data tabulky, pridavat polozky graficky nebo pomoci skriptu v editaci skriptu lze pomoci execute a zavření okna 
upravit nebo vytvořit přidání dat a v grafickém módu potom uložení dat znovu
7/průzkumnik serveru vymazání dat klinutím pravým tlačítkem na nový dotaz a v editačním módu dat prúzkumnik DELETE  FROM Zaměstnanci , kde Zaměstnanci je tabulka a execute zvolit
8/programování spočívá pomoci sqlconnection,sqlcommand,sqldatareader,executereader

Programátorska část kódu
-------------------------------------------------------------------------------------
  String connectionString= @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename='|DataDirectory|Database1.mdf';Integrated Security = True";
  List<string> dataList = new List<string>();

 private void button1_Click(object sender, EventArgs e)
        {
            listBox1.Items.Clear();
            dataList.Clear();
            listBox1.Items.Add("ID\tFirstName\tLastName\tAge\tEmai\tAddress\tPhone\tJob");
            dataList.Add("ID\tFirstName\tLastName\tAge\tEmai\tAddress\tPhone\tJob"); 

            string dotaz = "SELECT * FROM Osoby";
            SqlConnection spojeni = new SqlConnection(connectionString);
            spojeni.Open();
            SqlCommand prikaz = new SqlCommand(dotaz, spojeni);
            SqlDataReader dataReader = prikaz.ExecuteReader();
            //string data = "";
            int i = 0;
            while (dataReader.Read()){
                //data += dataReader[i] + "\t";
                //i++;
                listBox1.Items.Add(dataReader[0] + "\t" + dataReader[1] + "\t" + dataReader[2] + "\t" + dataReader[3] + "\t" + dataReader[4] + "\t" + dataReader[5] + "\t" +
                dataReader[6] + "\t" + dataReader[7]);
                dataList.Add(dataReader[0] + "\t" + dataReader[1] + "\t" + dataReader[2] + "\t" + dataReader[3] + "\t" + dataReader[4] + "\t" + dataReader[5] + "\t" +
                dataReader[6] + "\t" + dataReader[7]);
            }
        }
        
Programátorska část kódu - DataGridView
-------------------------------------------------------------------------------------  
     void LoadDatabase()
     {
         //LOAD TABLE FROM DATABASE
         //listBox1.Items.Clear();
         dataList.Clear();
         //listBox1.Items.Add("ID\tFirstName\tLastName\tAge\tEmai\tAddress\tPhone\tJob");
         //dataList.Add("ID\tFirstName\tLastName\tAge\tEmail\tAddress\tPhone\tJob");
         DataTable dataTable = new DataTable();
         dataTable.Columns.Add("Id");
         dataTable.Columns.Add("FirstName");
         dataTable.Columns.Add("LastName");
         dataTable.Columns.Add("Age");
         dataTable.Columns.Add("Email");
         dataTable.Columns.Add("Address");
         dataTable.Columns.Add("Phone");
         dataTable.Columns.Add("Job");

         string dotaz = "SELECT * FROM Osoby";
         SqlConnection spojeni = new SqlConnection(connectionString);
         spojeni.Open();
         ; SqlCommand prikaz = new SqlCommand(dotaz, spojeni);
         SqlDataReader dataReader = prikaz.ExecuteReader();
         //string data = "";
         int i = 0;
         string[] vstup = null;
         string radka = null;
         while (dataReader.Read())
         {
             dataList.Add(dataReader[0] + "\t" + dataReader[1] + "\t" + dataReader[2] + "\t" + dataReader[3] + "\t" + dataReader[4] + "\t" + dataReader[5] + "\t" +
             dataReader[6] + "\t" + dataReader[7]);

         }
         
         foreach (string s in dataList)
         {
             vstup = s.Split('\t');
             for (int j = 0; j < vstup.Length; j++)
             {
                 vstup[j] = vstup[j].Trim();
               
             }
             DataRow row = dataTable.NewRow();
             row["ID"] = vstup[0];
             row["FirstName"] =vstup[1];
             row["LastName"] = vstup[2];
             row["Age"] = vstup[3];
             row["Email"] = vstup[4];
             row["Address"] = vstup[5];
             row["Phone"] = vstup[6];
             row["Job"] = vstup[7];
             dataTable.Rows.Add(row);
             //radka = String.Join("\t", vstup);
             //listBox1.Items.Add(radka);
             //dataTable.Rows.Add(radka);

         }
         
         dataGridView1.DataSource = dataTable;
         
     
     }      




Dotazy MS-SQL Server T-SQL
------------------------------------------------------------------------------------------------------

1/
string dotaz = "SELECT * FROM Osoby";
SqlConnection spojeni = new SqlConnection(connectionString);
spojeni.Open();
SqlCommand prikaz = new SqlCommand(dotaz, spojeni);
SqlDataReader dataReader = prikaz.ExecuteReader();
dataReader.Read()
dataList.Add(dataReader[0] + "\t" + dataReader[1] + "\t" + dataReader[2] + "\t" + dataReader[3] + "\t" + dataReader[4] + "\t" + dataReader[5] + "\t" +
dataReader[6] + "\t" + dataReader[7]);

2/ 
string dotaz = "DELETE  FROM Osoby";
prikaz.ExecuteNonQuery();

3/
string dotaz = "INSERT INTO Osoby (FirstName, LastName, Age, Email, Address, Phone, Job) VALUES  (@FirstName, @LastName, @Age, @Email, @Address, @Phone, @Job)";
prikaz.Parameters.AddWithValue("@FirstName", FirstName);
prikaz.ExecuteNonQuery();

4/
string dotaz = "UPDATE Osoby SET FirstName=@FirstName, LastName=@LastName, Age=@Age, Email=@Email, Address=@Address, Phone=@Phone, Job=@Job WHERE Id=@Id";
prikaz.Parameters.AddWithValue("@Id", Id);
prikaz.ExecuteNonQuery();

5/
string dotaz = "DELETE FROM osoby WHERE ID=@ID";
prikaz.Parameters.AddWithValue("@Id", Id);
prikaz.ExecuteNonQuery();

DataGridView
------------------------------------------------------------------------------------------------------
DataTable dataTable = new DataTable();
dataTable.Columns.Add("Id");
dataTable.Columns.Add("FirstName");
dataTable.Columns.Add("LastName");
dataTable.Columns.Add("Age");
dataTable.Columns.Add("Email");
dataTable.Columns.Add("Address");
dataTable.Columns.Add("Phone");
dataTable.Columns.Add("Job");

DataRow row = dataTable.NewRow();
row["ID"] = vstup[0];
row["FirstName"] =vstup[1];
row["LastName"] = vstup[2];
row["Age"] = vstup[3];
row["Email"] = vstup[4];
row["Address"] = vstup[5];
row["Phone"] = vstup[6];
row["Job"] = vstup[7];
dataTable.Rows.Add(row);

dataGridView1.DataSource = dataTable;

Shrnutí DataGridView
DataTable dataTable = new DataTable();
dataTable.Columns.Add("Id");
DataRow row = dataTable.NewRow();
row["ID"] = vstup[0];
dataGridView1.DataSource = dataTable;


Data která lze opakovaně vkládat do databáze, aby se nemusela znovu psát nebo naklikávat.
------------------------------------------------------------------------------------------------------

Tabulka - T-SQL
CREATE TABLE [dbo].[Osoby]
(
	[Id] INT NOT NULL PRIMARY KEY IDENTITY, 
    [FirstName] NCHAR(20) NULL,
	[LastName] NCHAR(20) NULL,
	[Age] INT NULL,
	[Email] NCHAR(20) NULL,
	[Address] NCHAR(100) NULL,
	[Phone] INT  NULL,
	[Job] NCHAR(100) NULL,

)


Řešeni pokud chci provádět insert osdtraněním identity ale je třeba pak vytvořit znovu tabulku vymazáním ze server a databáze
aby zde nebylo 
SET IDENTITY_INSERT [dbo].[Osoby] ON

CREATE TABLE [dbo].[Osoby]
(
	[Id] INT NOT NULL PRIMARY KEY, 
    [FirstName] NCHAR(20) NULL,
	[LastName] NCHAR(20) NULL,
	[Age] INT NULL,
	[Email] NCHAR(20) NULL,
	[Address] NCHAR(100) NULL,
	[Phone] INT  NULL,
	[Job] NCHAR(100) NULL,

)

CREATE TABLE [dbo].[Osoby]
(
 [Id] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
     [FirstName] NCHAR(20) NULL,
	[LastName] NCHAR(20) NULL,
	[Age] INT NULL,
	[Email] NCHAR(20) NULL,
	[Address] NCHAR(100) NULL,
	[Phone] INT  NULL,
	[Job] NCHAR(100) NULL,

)


Data tabulky 
SET IDENTITY_INSERT [dbo].[Osoby] ON
INSERT INTO [dbo].[Osoby] ([Id], [FirstName], [LastName], [Age], [Email], [Address], [Phone], [Job]) VALUES (1, N'John                ', N'Doe                 ', 25, N'bla@bla.com         ', N'New York                                                                                            ', 563123, N'Programmer                                                                                          ')
INSERT INTO [dbo].[Osoby] ([Id], [FirstName], [LastName], [Age], [Email], [Address], [Phone], [Job]) VALUES (2, N'John                ', N'Smith               ', 45, N'bla@bla.cz          ', N'Prague                                                                                              ', 456123, N'Koder                                                                                               ')
INSERT INTO [dbo].[Osoby] ([Id], [FirstName], [LastName], [Age], [Email], [Address], [Phone], [Job]) VALUES (6, N'Bill                ', N'Gaqtes              ', 78, N'Bill@microsoft.com  ', N'Las Vegas                                                                                           ', 666666, N'Manager                                                                                             ')
INSERT INTO [dbo].[Osoby] ([Id], [FirstName], [LastName], [Age], [Email], [Address], [Phone], [Job]) VALUES (7, N'Neo                 ', N'Neo                 ', 125, N'neo@matrix.com      ', N'Matrix                                                                                              ', 999999, N'The one                                                                                             ')
SET IDENTITY_INSERT [dbo].[Osoby] OFF

SET IDENTITY_INSERT [dbo].[Zaměstnanci] ON
INSERT INTO [dbo].[Zaměstnanci] ([Id], [FirstName], [LastName], [Age], [Email], [Address], [Phone], [Job]) VALUES (1, N'John                ', N'Doe                 ', 25, N'bla@bla.com         ', N'New York                                                                                            ', 563123, N'Programmer                                                                                          ')
INSERT INTO [dbo].[Zaměstnanci] ([Id], [FirstName], [LastName], [Age], [Email], [Address], [Phone], [Job]) VALUES (2, N'John                ', N'Smith               ', 45, N'bla@bla.cz          ', N'Prague                                                                                              ', 456123, N'Koder                                                                                               ')
INSERT INTO [dbo].[Zaměstnanci] ([Id], [FirstName], [LastName], [Age], [Email], [Address], [Phone], [Job]) VALUES (6, N'Bill                ', N'Gaqtes              ', 78, N'Bill@microsoft.com  ', N'Las Vegas                                                                                           ', 666666, N'Manager                                                                                             ')
INSERT INTO [dbo].[Zaměstnanci] ([Id], [FirstName], [LastName], [Age], [Email], [Address], [Phone], [Job]) VALUES (7, N'Neo                 ', N'Neo                 ', 125, N'neo@matrix.com      ', N'Matrix                                                                                              ', 999999, N'The one                                                                                             ')
SET IDENTITY_INSERT [dbo].[Zaměstnanci] OFF

Dotazy  - T-SQL
SELECT * FROM Osoby
DELETE  FROM Zaměstnanci

prikaz2.Parameters.AddWithValue("@Job", Job);
SqlCommand prikaz = new SqlCommand(dotaz, spojeni);
prikaz.ExecuteNonQuery();

      string dotaz2 = "SET IDENTITY_INSERT Osoby ON; " +
           "INSERT INTO Osoby (Id, FirstName, LastName, Age, Email, Address, Phone, Job) VALUES  (@Id, @FirstName, @LastName, @Age, @Email, @Address, @Phone, @Job)" +
           "SET IDENTITY_INSERT Osoby OFF; ";
           
 string dotaz2 = "INSERT INTO Osoby (FirstName, LastName, Age, Email, Address, Phone, Job) VALUES (@FirstName, @LastName, @Age, @Email, @Address, @Phone, @Job)";